/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.study.dao;

import com.study.util.DButil;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.study.dto.OrderDTO;
import com.study.dto.Order_DetailDTO;
import com.study.dto.Order_Line_ItemDTO;
import com.study.util.DateUtil;

/**
 *
 * @author Truong Anh Tuan
 */
public class OrderDAO {

    public OrderDAO() {
    }
    Connection con = null;
    PreparedStatement pst = null;
    private final String SQL_READ_BY_STUDENT_ID = "select orders.ORDER_ID,orders.STUDENT_ID,orders.ORDER_DATE,orders.AMOUNT,orders.STATUS,order_line_items.ID,order_line_items.COURSE_ID,order_line_items.FEE,courses.COURSE_NAME from orders inner join order_line_items on orders.ORDER_ID = order_line_items.ORDER_ID inner join courses on order_line_items.COURSE_ID = courses.COURSE_ID where STUDENT_ID = ?";
    private final String SQL_CREATE_ORDERS = "INSERT INTO orders(STUDENT_ID, STATUS) " +
            "VALUES(?, ?)";
    private final String SQL_CREATE_ORDERS_LINE_ITEMS = "INSERT INTO order_line_items(COURSE_ID, ORDER_ID, FEE) " +
            "VALUES(?, ?, ?)";

    public ArrayList<Order_DetailDTO> Read_By_Student_Id(String id) {
        try {
            ArrayList<Order_DetailDTO> order = new ArrayList<Order_DetailDTO>();
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_READ_BY_STUDENT_ID);
            pst.setString(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Order_DetailDTO dto = new Order_DetailDTO();
                dto.setOrderId(rs.getInt(1));
                dto.setStudentId(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setOrderDate("");
                } else {
                    dto.setOrderDate(DateUtil.convert2String(date, "MM-dd-yyyy"));
                }
                dto.setAmount(rs.getInt(4));
                dto.setStatus(rs.getString(5));
                dto.setId(rs.getInt(6));
                dto.setCoursesId(rs.getString(7));
                dto.setFee(rs.getFloat(8));
                dto.setCoursesName(rs.getString(9));
                order.add(dto);
            }
            return order;
        } catch (SQLException ex) {
            Logger.getLogger(EnrollmentDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DButil.closeConnection(con);
        }
    }

    public boolean Create(OrderDTO order, Order_Line_ItemDTO order_line_item) {
        try {
            con = DButil.GetConnection();
            con.setAutoCommit(false);

            PreparedStatement pst_order = con.prepareStatement(SQL_CREATE_ORDERS);
            pst_order.setString(1, order.getStudentId());
            pst_order.setDate(2, DateUtil.convert2Date(order.getOrderDate(), "MM-dd-yyy"));
            pst_order.setInt(3, order.getAmount());
            pst_order.setString(4, order.getStatus());
            pst_order.executeUpdate();

            PreparedStatement pst_order_line_item = con.prepareStatement(SQL_CREATE_ORDERS_LINE_ITEMS);
            pst_order_line_item.setString(1, order_line_item.getCoursesId());
            pst_order_line_item.setInt(2, order_line_item.getOrderId());
            pst.setFloat(3, order_line_item.getFee());
            pst_order_line_item.executeUpdate();

            con.commit();
            con.setAutoCommit(true);

            return true;


        } catch (SQLException ex) {
            try {
                Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
                con.rollback();
                return false;
            } catch (SQLException ex1) {
                Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex1);
                return false;
            }
        } finally {
            DButil.closeConnection(con);
        }

    }

    public boolean Create(String stId, String csId) {
        try {
            con = DButil.GetConnection();
            con.setAutoCommit(false);

            PreparedStatement pst_order = con.prepareStatement(SQL_CREATE_ORDERS);
            pst_order.setString(1, stId);
            //date time
            //Calendar cal = Calendar.getInstance();
            //SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyy");
            //String dt = cal.getTime().toString();
            //pst_order.setDate(2, DateUtil.convert2Date(dt, "MM-dd-yyy"));

            //pst_order.setInt(3, order.getAmount());
            pst_order.setString(2, "ok");
            pst_order.executeUpdate();
            //lay id vua moi insert vao bang
            String identity = "SELECT @@IDENTITY";
            Statement st = con.createStatement();
            ResultSet rs1 = st.executeQuery(identity);

            int ordId = rs1.getInt(1);
            //ket thuc lay id vua moi insert vao bang
            PreparedStatement pst_order_line_item = con.prepareStatement(SQL_CREATE_ORDERS_LINE_ITEMS);
            pst_order_line_item.setString(1, csId);
            pst_order_line_item.setInt(2, ordId);
            // pst.setFloat(3, order_line_item.getFee());
            pst_order_line_item.executeUpdate();

            con.commit();
            con.setAutoCommit(true);

            return true;


        } catch (SQLException ex) {
            try {
                Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
                con.rollback();
                return false;
            } catch (SQLException ex1) {
                Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex1);
                return false;
            }
        } finally {
            DButil.closeConnection(con);
        }

    }
}
